package nl.hro.mobiqueen.data;

import nl.hro.mobiqueen.generic.ApplicationException;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseAdapter {
	private static final String TAG = "DatabaseAdapter";

	private static final String DATABASE_NAME = "mobiqueendb";
	private static final int DATABASE_VERSION = 9;

	private static final String TABLE_ARTIST = "artist";
	private static final String TABLE_EVENT = "event";
	private static final String TABLE_PODIUM = "podium";

	public static final String KEY_ID = "id";
	public static final String KEY_NAME = "name";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_STREET = "street";
	public static final String KEY_HOUSENR = "housenr";
	public static final String KEY_ZIPCODE = "zipcode";
	public static final String KEY_CITY = "city";
	public static final String KEY_ARTIST = "artist";
	public static final String KEY_PODIUM = "podium";
	public static final String KEY_START_TIME = "start_time";
	public static final String KEY_END_TIME = "end_time";
	public static final String KEY_GENRE = "genre";

	private static final String DATABASE_CREATE_ARTIST = ""
			+ "CREATE TABLE artist ("
			+ "	id integer primary key autoincrement, "
			+ "	name varchar not null," + " description text not null);";

	private static final String DATABASE_CREATE_PODIUM = ""
			+ "CREATE TABLE podium ("
			+ " id integer primary key autoincrement, "
			+ " name varchar not null, " + " latitude real, "
			+ " longitude real, " + " street varchar, " + " housenr varchar, "
			+ " zipcode varchar, " + " city varchar);";

	private static final String DATABASE_CREATE_EVENT = ""
			+ "CREATE TABLE event ("
			+ " id integer primary key autoincrement, "
			+ " podium integer not null, " + " artist integer not null, "
			+ " start_time varchar not null, " + " end_time varchar not null, "
			+ " genre varchar not null,"
			+ " FOREIGN KEY (podium) REFERENCES podium(id),"
			+ " FOREIGN KEY (artist) REFERENCES artist(id));";

	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DatabaseAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DATABASE_CREATE_ARTIST);
				db.execSQL(DATABASE_CREATE_PODIUM);
				db.execSQL(DATABASE_CREATE_EVENT);
			} catch (SQLiteException e) {
				Log.e("createerr", e.toString());
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_ARTIST);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_PODIUM);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENT);
			onCreate(db);
		}
	}

	// ---opens the database---
	public DatabaseAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}

	/**
	 * Delete Event
	 */
	public void deleteEvent(int id) {
		db.delete(TABLE_EVENT, KEY_ID + "=" + id, null);
	}

	/**
	 * Delete Podium
	 */
	public void deletePodium(int id) throws ApplicationException {
		Cursor c = getEventsForPodium(id);
		c.moveToFirst();
		boolean performanceExistsForPodium = c.getCount() > 0 ? true : false;
		if (performanceExistsForPodium)
			throw new ApplicationException(
					"Referential integrity fault. Delete not allowed.");

		db.delete(TABLE_PODIUM, KEY_ID + "=" + id, null);
	}

	/**
	 * Find Artist
	 */
	public Cursor findArtist(int id) {
		Cursor cursor = db.query(true, TABLE_ARTIST, new String[] { KEY_ID,
				KEY_NAME, KEY_DESCRIPTION }, KEY_ID + "=" + id, null, null,
				null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Find Event
	 */
	public Cursor findEvent(int id) throws SQLException {
		Cursor cursor = db.query(true, TABLE_EVENT,
				new String[] { KEY_ID, KEY_PODIUM, KEY_ARTIST, KEY_START_TIME,
						KEY_END_TIME, KEY_GENRE }, KEY_ID + "=" + id, null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Find Podium
	 */
	public Cursor findPodium(int id) throws SQLException {
		Cursor cursor = db.query(true, TABLE_PODIUM, new String[] { KEY_ID,
				KEY_NAME, KEY_LATITUDE, KEY_LONGITUDE, KEY_STREET, KEY_HOUSENR,
				KEY_ZIPCODE, KEY_CITY, }, KEY_ID + "=" + id, null, null, null,
				null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Insert Artist
	 */
	public void insertArtist(int id, String name, String description) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		db.insert(TABLE_ARTIST, null, values);
	}

	/**
	 * Insert Event
	 */
	public void insertEvent(int id, int podium, int artist, String startTime,
			String endTime, String genre) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_PODIUM, podium);
		values.put(KEY_ARTIST, artist);
		values.put(KEY_START_TIME, startTime);
		values.put(KEY_END_TIME, endTime);
		values.put(KEY_GENRE, genre);
		db.insert(TABLE_EVENT, null, values);
	}

	/**
	 * Insert Podium
	 */
	public void insertPodium(int id, String name, Double lat, Double lon,
			String street, String housenr, String zipcode, String city) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_NAME, name);
		values.put(KEY_LATITUDE, lat);
		values.put(KEY_LONGITUDE, lon);
		values.put(KEY_STREET, street);
		values.put(KEY_HOUSENR, housenr);
		values.put(KEY_ZIPCODE, zipcode);
		values.put(KEY_CITY, city);
		db.insert(TABLE_PODIUM, null, values);
	}

	/**
	 * List Artists
	 */
	public Cursor listArtists() {
		return db.query(TABLE_ARTIST, new String[] { KEY_ID, KEY_NAME,
				KEY_DESCRIPTION }, null, null, null, null, null);
	}

	/**
	 * List Events
	 */
	public Cursor listEvents() {
		return db.query(TABLE_EVENT, new String[] { KEY_ID, KEY_PODIUM,
				KEY_ARTIST, KEY_START_TIME, KEY_END_TIME, KEY_GENRE }, null,
				null, null, null, null);
	}

	/**
	 * List Podia
	 */
	public Cursor listPodia() {
		return db.query(TABLE_PODIUM, new String[] { KEY_ID, KEY_NAME,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_STREET, KEY_HOUSENR,
				KEY_ZIPCODE, KEY_CITY, }, null, null, null, null, null);
	}

	/**
	 * Update Artist
	 */
	public void updateArtist(int id, String name, String description) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		db.update(TABLE_ARTIST, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Update Event
	 */
	public void updateEvent(int id, int podium, int artist, String startTime,
			String endTime, String genre) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_PODIUM, podium);
		values.put(KEY_ARTIST, artist);
		values.put(KEY_START_TIME, startTime);
		values.put(KEY_START_TIME, endTime);
		values.put(KEY_GENRE, genre);
		db.update(TABLE_EVENT, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Update Podium
	 */
	public void updatePodium(int id, String name, Double lat, Double lon,
			String street, String housenr, String zipcode, String city) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_LATITUDE, lat);
		values.put(KEY_LONGITUDE, lon);
		values.put(KEY_STREET, street);
		values.put(KEY_HOUSENR, housenr);
		values.put(KEY_ZIPCODE, zipcode);
		values.put(KEY_CITY, city);
		db.update(TABLE_PODIUM, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Retrieves all events of a given podium
	 */
	public Cursor getEventsForPodium(int podiumId) throws SQLException {
		Cursor cursor = db.query(true, TABLE_EVENT,
				new String[] { KEY_ID, KEY_PODIUM, KEY_ARTIST, KEY_START_TIME,
						KEY_END_TIME, KEY_GENRE }, KEY_PODIUM + "=" + podiumId,
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
}